﻿using System;
using System.Text;

namespace MyFastDataSourceBind
{
    /// <summary>
    /// 提供拼凑SQL语句的例程，编写袁永福
    /// </summary>
    public class SQLTextBuilder
    {
        private static string strConditionParameterPrefix = "Key_";
        /// <summary>
        /// 查询条件参数名称前缀
        /// </summary>
        public static string ConditionParameterPrefix
        {
            get
            {
                return strConditionParameterPrefix; 
            }
            set
            {
                strConditionParameterPrefix = value; 
            }
        }

        /// <summary>
        /// 创建查询数据用的SQL文本
        /// </summary>
        /// <remarks>
        /// 本函数用于生成“Select Field1 , Field2 , Field3 From Table 
        /// Where Key1 = ?(或 @Key_Field1) and Key2 = ?(或@Key_Field2 )”
        /// 样式的字符串
        /// </remarks>
        /// <param name="TableName">数据表名</param>
        /// <param name="FieldNames">字段列表</param>
        /// <param name="ConditionFieldNames">查询条件字段列表</param>
        /// <param name="NamedParameter">是否使用命名参数</param>
        /// <returns>生成的SQL语句</returns>
        public static string BuildSelect(
            string TableName,
            string[] FieldNames,
            string[] ConditionFieldNames,
            bool NamedParameter)
        {
            if (TableName == null || TableName.Length == 0)
            {
                throw new ArgumentNullException("TableName");
            }
            if (FieldNames == null || FieldNames.Length == 0)
            {
                throw new ArgumentNullException("FieldNames");
            }
            StringBuilder mySQL = new StringBuilder();
            mySQL.Append("Select " + Concat(FieldNames, " , "));
            mySQL.Append(BuildCondition(ConditionFieldNames, NamedParameter));
            mySQL.Append(" From " + TableName);
            return mySQL.ToString();
        }

        /// <summary>
        /// 创建插入数据用的SQL文本
        /// </summary>
        /// <remarks>
        /// 本函数用于生成“Insert Into Table ( Field1 , Field2 ) 
        /// Values ( ?[或 @Field1 ] , ?[或 @Field2])”
        /// 样式的字符串
        /// </remarks>
        /// <param name="TableName">数据表名</param>
        /// <param name="FieldNames">字段列表</param>
        /// <param name="NamedParameter">是否用命名参数</param>
        /// <returns>生成的SQL语句</returns>
        public static string BuildInsert(
            string TableName,
            string[] FieldNames,
            bool NamedParameter)
        {
            if (TableName == null || TableName.Length == 0)
            {
                throw new ArgumentNullException("TableName");
            }
            if (FieldNames == null || FieldNames.Length == 0)
            {
                throw new ArgumentNullException("FieldNames");
            }
            StringBuilder mySQL = new StringBuilder();
            mySQL.Append("Insert Into " + TableName + " ( ");
            mySQL.Append(Concat(FieldNames, " , "));
            mySQL.Append(" ) Values ( ");
            for (int iCount = 0; iCount < FieldNames.Length; iCount++)
            {
                if (iCount > 0)
                {
                    mySQL.Append(" , ");
                }
                if (NamedParameter)
                {
                    mySQL.Append("@" + FieldNames[iCount]);
                }
                else
                {
                    mySQL.Append("?");
                }
            }
            mySQL.Append(" )");
            return mySQL.ToString();
        }

        /// <summary>
        /// 创建删除数据用的SQL文本
        /// </summary>
        /// <remarks>
        /// 本函数用于生成“Delete From Table 
        /// Where Field1=?(或 @Key_Field1) And Field2=?(或 @Key_Field2)”
        /// 样式的字符串
        /// </remarks>
        /// <param name="TableName">数据表名</param>
        /// <param name="ConditionFieldNames">查询条件用的字段列表</param>
        /// <param name="NamedParameter">是否用命名参数</param>
        /// <returns>生成的SQL文本</returns>
        public static string BuildDelete(
            string TableName,
            string[] ConditionFieldNames,
            bool NamedParameter)
        {
            if (TableName == null || TableName.Length == 0)
            {
                throw new ArgumentNullException("TableName");
            }
            StringBuilder mySQL = new StringBuilder();
            mySQL.Append("Delete From " + TableName);
            mySQL.Append(BuildCondition(ConditionFieldNames, NamedParameter));
            return mySQL.ToString();
        }

        /// <summary>
        /// 创建更新数据库用的SQL文本
        /// </summary>
        /// <remarks>
        /// 本函数用于生成“Update Table SET Field1=?(或 @Field1) , Field2=?(或 @Field2)
        /// Where Key1=?(或 @Key1) And Key2=?(或 @Key2)”样式的字符串。
        /// </remarks>
        /// <param name="TableName">数据表名</param>
        /// <param name="FieldNames">字段列表</param>
        /// <param name="ConditionFieldNames">查询条件字段列表</param>
        /// <param name="NamedParameter">是否用命名参数</param>
        /// <returns>生成的SQL文本</returns>
        public static string BuildUpdate(
            string TableName,
            string[] FieldNames,
            string[] ConditionFieldNames,
            bool NamedParameter)
        {
            if (TableName == null || TableName.Length == 0)
            {
                throw new ArgumentNullException("TableName");
            }
            if (FieldNames == null || FieldNames.Length == 0)
            {
                throw new ArgumentNullException("FieldNames");
            }

            StringBuilder mySQL = new StringBuilder();
            mySQL.Append("Update " + TableName + " SET ");
            for (int iCount = 0; iCount < FieldNames.Length; iCount++)
            {
                if (iCount > 0)
                {
                    mySQL.Append(" , ");
                }
                mySQL.Append(FieldNames[iCount]);
                if (NamedParameter)
                {
                    mySQL.Append(" = @" + FieldNames[iCount]);
                }
                else
                {
                    mySQL.Append(" = ?");
                }
            }
            mySQL.Append(BuildCondition(ConditionFieldNames, NamedParameter));
            return mySQL.ToString();
        }

        /// <summary>
        /// 生成 Where 子语句
        /// </summary>
        /// <remarks>
        /// 本函数用于生成“Where Field1=?(或 @Key_Field1) And Field2=?(或 @Key_Field2)”
        /// 样式的字符串。
        /// </remarks>
        /// <param name="FieldNames">字段名</param>
        /// <param name="NamedParameter">是否采用命名参数</param>
        /// <returns>生成的SQL语句</returns>
        private static string BuildCondition(string[] FieldNames, bool NamedParameter)
        {
            if (FieldNames == null || FieldNames.Length == 0)
            {
                return "";
            }
            StringBuilder mySQL = new StringBuilder();
            mySQL.Append(" Where ");
            for (int iCount = 0; iCount < FieldNames.Length; iCount++)
            {
                if (iCount > 0)
                {
                    mySQL.Append(" And ");
                }
                mySQL.Append(FieldNames[iCount]);
                if (NamedParameter)
                {
                    mySQL.Append(" = @" + strConditionParameterPrefix + FieldNames[iCount]);
                }
                else
                {
                    mySQL.Append(" = ?");
                }
            }
            mySQL.Append(" ");
            return mySQL.ToString();
        }
        /// <summary>
        /// 连接多个字符串
        /// </summary>
        /// <remarks>
        /// 本函数用于生成“item1,item2,item3”样式的字符串。
        /// </remarks>
        /// <param name="items">字符串项目数组</param>
        /// <param name="Splitter">各个字符串项目中间的连接字符</param>
        /// <returns>生成的大字符串</returns>
        private static string Concat(string[] items, string Splitter)
        {
            StringBuilder myStr = new StringBuilder();
            foreach (string item in items)
            {
                if (myStr.Length > 0 && Splitter != null)
                {
                    myStr.Append(Splitter);
                }
                myStr.Append(item);
            }
            return myStr.ToString();
        }
    }
}